﻿using System;
using System.Data;
using System.Data.Common;

using ZhangBY.Config;
using ZhangBY.Common;
using ZhangBY.Entity;

namespace ZhangBY.Data.SqlServer
{

    public partial class DataProvider : IDataProvider
    {
        private static int _lastRemoveTimeout;//记录上一次清除离线用户时间

        /// <summary>
        /// SQL SERVER SQL语句转义
        /// </summary>
        /// <param name="str">需要转义的关键字符串</param>
        /// <param name="pattern">需要转义的字符数组</param>
        /// <returns>转义后的字符串</returns>
        private static string RegEsc(string str)
        {
            string[] pattern = { @"%", @"_", @"'" };
            foreach (string s in pattern)
            {
                switch (s)
                {
                    case "%":
                        str = str.Replace(s, "[%]");
                        break;
                    case "_":
                        str = str.Replace(s, "[_]");
                        break;
                    case "'":
                        str = str.Replace(s, "['']");
                        break;
                }
            }
            return str;
        }

        #region 普通用户管理

        /// <summary>
        /// 检查密码
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="password">密码</param>
        /// <param name="originalpassword">是否非MD5密码</param>
        /// <returns>如果正确则返回用户id, 否则返回-1</returns>
        public IDataReader CheckPasswordByUsername(string userName, string passWord, bool originalPassWord)
        {
            DbParameter[] parms = {                                     
									   DbHelper.MakeInParam("@username",(DbType)SqlDbType.Char,60, userName),
									   DbHelper.MakeInParam("@userpass",(DbType)SqlDbType.Char,60, originalPassWord ? Utils.MD5(passWord) : passWord)
								   };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, "[dbo].[sp_checkpasswordandusername]", parms);
        }

        /// <summary>
        /// 检查密码
        /// </summary>
        /// <param name="username">用户id</param>
        /// <param name="password">密码</param>
        /// <param name="originalpassword">是否非MD5密码</param>
        /// <returns>如果正确则返回用户id, 否则返回-1</returns>
        public IDataReader CheckPasswordById(string userId, string passWord, bool originalPassWord)
        {
            DbParameter[] parms = {                                     
									   DbHelper.MakeInParam("@userid",(DbType)SqlDbType.Char,60, userId),
									   DbHelper.MakeInParam("@userpass",(DbType)SqlDbType.Char,60, originalPassWord ? Utils.MD5(passWord) : passWord)
								   };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, "[dbo].[sp_checkpasswordanduserid]", parms);
        }

        ///// <summary>
        ///// 返回指定用户的信息
        ///// </summary>
        ///// <param name="uid">用户id</param>
        ///// <returns>用户信息</returns>
        //public IDataReader GetUserInfoToReader(string uid)
        //{
        //    DbParameter[] parms = {
        //                               DbHelper.MakeInParam("@uid", (DbType)SqlDbType.NChar,60, uid)
        //                          };
        //    return DbHelper.ExecuteReader(CommandType.StoredProcedure, "[dbo].[sp_checkpasswordandusername]", parms);
        //}

        /// <summary>
        /// 获取简短用户信息
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <returns>用户简短信息</returns>
        public IDataReader GetShortUserInfoToReader(string uid)
        {
            DbParameter[] parms = {
									   DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NChar,60, uid),
			                      };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, "[dbo].[sp_getshortuserinfo]", parms);
        }

        /// <summary>
        /// 获取完整用户信息
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <returns>用户完整信息</returns>
        public IDataReader GetUserInfoToReader(string uid)
        {
            DbParameter[] parms = {
									   DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NChar,60, uid),
			                      };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, "[dbo].[sp_getuserinfo]", parms);
        }

        /// <summary>
        /// 根据用户名返回用户间短信息
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public IDataReader GetShortUserInfoByName(string userName)
        {
            DbParameter[] parms = {
									   DbHelper.MakeInParam("@username",(DbType)SqlDbType.VarChar,40,userName),
                                  };
            //string commandText = string.Format("SELECT TOP 1 {0} FROM [{1}user] WHERE [username]=@username",
            //                                    DbFields.USERS,
            //                                    BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, "[dbo].[sp_getShortUserInfoByName]", parms);
        }

        /// <summary>
        /// 根据用户Id返回用户间短信息
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public IDataReader GetShortUserInfoByUserId(string userId)
        {
            DbParameter[] parms = {
									   DbHelper.MakeInParam("@userid",(DbType)SqlDbType.VarChar,40,userId),
                                  };
            return DbHelper.ExecuteReader(CommandType.StoredProcedure, "[dbo].[sp_getShortUserInfoByUserid]", parms);
        }

        /// <summary>
        /// 执行在线用户向表及缓存中添加的操作.
        /// 如果timeout为负数则代表不需要精确更新用户是否在线的状态.
        /// </summary>
        /// <param name="onlineuserinfo">在组用户信息内容</param>
        /// <param name="timeout">系统设置用户多少时间即算做离线</param>
        /// <param name="deletingfrequency">删除过期用户频率(单位:分钟)</param>
        /// <returns>添加成功则返回刚刚添加的olid,失败则返回0</returns>
        public int AddOnlineUser(OnlineUserInfo onlineUserInfo, int timeOut, int deletingFrequency)
        {
            //标识需要更新用户在线状态，0表示需要更新
            int onlinestate = 1;

            // 如果timeout为负数则代表不需要精确更新用户是否在线的状态
            if (timeOut > 0)
            {
                if (onlineUserInfo.Userid!=null)
                    onlinestate = 0;
            }
            else
                timeOut = timeOut * -1;

            if (timeOut > 9999)
                timeOut = 9999;

            DbParameter[] parms = {
									   DbHelper.MakeInParam("@onlinestate",(DbType)SqlDbType.Int,4,onlinestate),
									   DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,onlineUserInfo.Userid),
									   DbHelper.MakeInParam("@userip",(DbType)SqlDbType.VarChar,15,onlineUserInfo.Ip),
									   DbHelper.MakeInParam("@username",(DbType)SqlDbType.NVarChar,40,onlineUserInfo.Username),
									   DbHelper.MakeInParam("@nickname",(DbType)SqlDbType.NVarChar,40,onlineUserInfo.Nickname),
									   DbHelper.MakeInParam("@userpass",(DbType)SqlDbType.Char,32,onlineUserInfo.Password),
									   DbHelper.MakeInParam("@requesttime",(DbType)SqlDbType.DateTime,30,Utils.StringToDateTime(onlineUserInfo.RequestTime))
								   };
            int olid = TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure,"sp_createonlineuser", parms));

            //按照系统设置频率(默认5分钟)清除过期用户
            if (_lastRemoveTimeout == 0 || (System.Environment.TickCount - _lastRemoveTimeout) > 60000 * deletingFrequency)
            {
                DeleteExpiredOnlineUsers(timeOut);
                _lastRemoveTimeout = System.Environment.TickCount;
            }
            // 如果id值太大则重建在线表
            if (olid > 2147483000)
            {
                this.CreateOnlineTable();
                DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "sp_createonlineuser", parms);
                return 1;
            }
            return 0;
        }

        /// <summary>
        /// 创建在线表,失败返回-1
        /// </summary>
        /// <returns>失败返回-1</returns>
        public int CreateOnlineTable()
        {
            try
            {
                return DbHelper.ExecuteNonQuery(CommandType.Text, string.Format("TRUNCATE TABLE [{0}online]", BaseConfigs.GetTablePrefix));
            }
            catch
            {
                return -1;
            }
        }

        /// <summary>
        /// 删除在线用户
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int DelOnlineUser(string userid)
        {
            DbParameter param = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NChar,60, userid);
            return DbHelper.ExecuteNonQuery(CommandType.Text,string.Format("DELETE FROM [dbo].[{0}online] WHERE [userid]=@userid",BaseConfigs.GetTablePrefix),param);
        }

        /// <summary>
        /// 清除超时用户
        /// </summary>
        /// <param name="timeOut">超时时间（分钟）</param>
        private void DeleteExpiredOnlineUsers(int timeOut)
        {
            System.Text.StringBuilder timeoutStrBuilder = new System.Text.StringBuilder();//记录超时用户个数
            System.Text.StringBuilder memberStrBuilder = new System.Text.StringBuilder();//记录超时用户userid

            DbParameter param = DbHelper.MakeInParam("@expires", (DbType)SqlDbType.DateTime, 8, DateTime.Now.AddMinutes(timeOut * -1));
            IDataReader dr = DbHelper.ExecuteReader(CommandType.StoredProcedure,"sp_getexpiredonlineuserlist",param);
            while (dr.Read())
            {
                timeoutStrBuilder.Append(",");//“,”的作用是分组
                timeoutStrBuilder.Append(dr["olid"].ToString());//从数据库中读取超时用户OLID
                if (dr["userid"].ToString()+"" != "")
                {
                    memberStrBuilder.Append(",");
                    memberStrBuilder.Append(dr["userid"].ToString());
                }
            }
            dr.Close();

            if (timeoutStrBuilder.Length > 0)
            {
                timeoutStrBuilder.Remove(0, 1);
                DbHelper.ExecuteNonQuery(CommandType.StoredProcedure,"sp_deleteonlineusers", DbHelper.MakeInParam("@olidlist", (DbType)SqlDbType.VarChar, 5000, timeoutStrBuilder.Length <= 5000 ? timeoutStrBuilder.ToString() : timeoutStrBuilder.ToString().Substring(0, 5000).TrimEnd(',')));
            }
            if (memberStrBuilder.Length > 0)
            {
                memberStrBuilder.Remove(0, 1);
                DbHelper.ExecuteNonQuery(CommandType.StoredProcedure,"sp_updateuseronlinestates",  DbHelper.MakeInParam("@uidlist", (DbType)SqlDbType.VarChar, 5000, memberStrBuilder.Length <= 5000 ? memberStrBuilder.ToString() : memberStrBuilder.ToString().Substring(0, 5000).TrimEnd(',')));
            }
        }

        /// <summary>
        /// 设置用户在线状态
        /// </summary>
        /// <param name="uid">用户Id</param>
        /// <param name="onlinestate">在线状态，1在线</param>
        /// <returns></returns>
        public int SetUserOnlineState(string userid, int onlineState)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@onlinestate",(DbType)SqlDbType.Int,2,onlineState),
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NChar,60,userid)
                                  };
            string commandText = string.Format("UPDATE [{0}online] SET [onlinestate]=@onlinestate,[requesttime]=GETDATE() WHERE [userid]=@userid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText,parms);
        }

        /// <summary>
        /// 获得指定用户的详细信息
        /// </summary>
        /// <param name="userId">在线用户ID</param>
        /// <param name="ip">IP</param>
        /// <returns></returns>
        public DataTable GetOnlineUserByIP(string userId, string ip)
        {
            DbParameter[] parms = { 
                                        DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NChar, 60, userId),
                                        DbHelper.MakeInParam("@userip", (DbType)SqlDbType.VarChar, 15, ip)
                                    };
            return DbHelper.ExecuteDataset(CommandType.StoredProcedure,"sp_getonlineuserbyip", parms).Tables[0];
        }

        /// <summary>
        /// 获得指定用户的详细信息
        /// </summary>
        /// <param name="userId">在线用户ID</param>
        /// <param name="password">用户密码</param>
        /// <returns>用户的详细信息</returns>
        public DataTable GetOnlineUser(string userId, string passWord)
        {
            DbParameter[] parms =  { 
                                        DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NChar, 60, userId),
                                        DbHelper.MakeInParam("@userpass", (DbType)SqlDbType.Char, 32, passWord)
                                    };
            return DbHelper.ExecuteDataset(CommandType.StoredProcedure, "sp_getonlineuser", parms).Tables[0];
        }

        /// <summary>
        /// 更新用户IP地址
        /// </summary>
        /// <param name="olid">在线id</param>
        /// <param name="ip">ip地址</param>
        public void UpdateIP(int olId, string ip)
        {
            DbParameter[] parms = {
									   DbHelper.MakeInParam("@ip",(DbType)SqlDbType.VarChar,15,ip),
									   DbHelper.MakeInParam("@olid",(DbType)SqlDbType.Int,4,olId)
								   };
            string commandText = string.Format("UPDATE [{0}online] SET [userip]=@ip WHERE [olid]=@olid", BaseConfigs.GetTablePrefix);
            DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 更新用户的当前动作及相关信息
        /// </summary>
        /// <param name="olid">在线列表id</param>
        /// <param name="action">动作</param>
        public void UpdateAction(int olId, int action)
        {
            DbParameter[] parms = {
                                           //DbHelper.MakeInParam("@action",(DbType)SqlDbType.SmallInt,2,action),
                                           //DbHelper.MakeInParam("@requesttime", (DbType)SqlDbType.DateTime, 8, DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))),
										   DbHelper.MakeInParam("@olid",(DbType)SqlDbType.Int,4,olId)
								  };
            DbHelper.ExecuteNonQuery(CommandType.StoredProcedure,"sp_updateonlineaction", parms);
        }

        /// <summary>
        /// 删除符合条件的一个或多个用户信息
        /// </summary>
        /// <returns>删除行数</returns>
        public int DeleteRowsByIP(string ip)
        {
            DbParameter[] parms = {
									   DbHelper.MakeInParam("@userip",(DbType)SqlDbType.VarChar,15,ip)
								   };
            if (ip != "0.0.0.0")
                return DbHelper.ExecuteNonQuery(CommandType.Text, string.Format("DELETE FROM [dbo].[{0}online] WHERE [userid]=null AND [userip]=@userip", BaseConfigs.GetTablePrefix), parms);

            return 0;
        }

        /// <summary>
        /// 更新用户密码
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="userpass"></param>
        /// <returns></returns>
        public int UpUserPass(string userid, string userpass)
        {
            DbParameter[] getParms = { 
                                         DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NChar,60,userid),
                                         DbHelper.MakeInParam("@userpass",(DbType)SqlDbType.NChar,20,userpass)
                                     };
            return DbHelper.ExecuteNonQuery(CommandType.Text,string.Format("UPDATE [dbo].[{0}user] SET [userpass]=@userpass WHERE [userid]=@userid",BaseConfigs.GetTablePrefix),getParms);
        }

        /// <summary>
        /// 更新我的用户信息
        /// </summary>
        /// <returns></returns>
        public int UpUserInfo(string userid,string userpass,string address,string area,string email,string fax,string introduction,string nationalities,string name,string phone,string qq,short sex)
        {
            DbParameter[] getParms = {
                                         DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NChar,60,userid),
                                         DbHelper.MakeInParam("@userpass",(DbType)SqlDbType.NChar,20,userpass),
                                         DbHelper.MakeInParam("@address",(DbType)SqlDbType.NChar,100,address),
                                         DbHelper.MakeInParam("@area",(DbType)SqlDbType.NChar,50,area),
                                         DbHelper.MakeInParam("@email",(DbType)SqlDbType.NChar,40,email),
                                         DbHelper.MakeInParam("@fax",(DbType)SqlDbType.NChar,16,fax),
                                         DbHelper.MakeInParam("@introduction",(DbType)SqlDbType.NChar,100,introduction),
                                         DbHelper.MakeInParam("@nationalities",(DbType)SqlDbType.NChar,50,nationalities),
                                         DbHelper.MakeInParam("@name",(DbType)SqlDbType.NChar,20,name),
                                         DbHelper.MakeInParam("@phone",(DbType)SqlDbType.NChar,16,phone),
                                         DbHelper.MakeInParam("@qq",(DbType)SqlDbType.NChar,16,qq),
                                         DbHelper.MakeInParam("@sex",(DbType)SqlDbType.TinyInt,8,sex),
                                         DbHelper.MakeOutParam("@outnum",(DbType)SqlDbType.Int,8)
                                     };
            DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "sp_updateuseroruserinfo", getParms);
            return Convert.ToInt32(getParms[getParms.Length - 1].Value);
 
        }

        /// <summary>
        /// 重设用户密码
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="userpass"></param>
        /// <returns></returns>
        public int SetNewPass(string userid,string userpass)
        {
            DbParameter[] getParms ={
                                       DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NChar,60,userid),
                                       DbHelper.MakeInParam("@userpass",(DbType)SqlDbType.NChar,20,userpass)
                                   };
            return DbHelper.ExecuteNonQuery(CommandType.Text, string.Format("UPDATE [dbo].[{0}user] SET [userpass]=@userpass WHERE [userid]=@userid", BaseConfigs.GetTablePrefix), getParms);
        }

        /// <summary>
        /// 更新安全问题
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="questioners"></param>
        /// <returns></returns>
        public int SetNewSafeQuestion(string userid, string questioners)
        {
            DbParameter[] getParms ={
                                       DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NChar,60,userid),
                                       DbHelper.MakeInParam("@questioners",(DbType)SqlDbType.NChar,40,questioners)
                                   };
            return DbHelper.ExecuteNonQuery(CommandType.Text, string.Format("UPDATE [dbo].[{0}user] SET [questioners]=@questioners WHERE [userid]=@userid", BaseConfigs.GetTablePrefix), getParms);
        }

        /// <summary>
        /// 更新安全答案
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="answers"></param>
        /// <returns></returns>
        public int SetNewSafeAnswers(string userid, string answers)
        {
            DbParameter[] getParms ={
                                       DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NChar,60,userid),
                                       DbHelper.MakeInParam("@answers",(DbType)SqlDbType.NVarChar,50,answers)
                                   };
            return DbHelper.ExecuteNonQuery(CommandType.Text, string.Format("UPDATE [dbo].[{0}user] SET [answers]=@answers WHERE [userid]=@userid", BaseConfigs.GetTablePrefix), getParms);
        }

        /// <summary>
        /// 更新安全问题、答案
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="questioners"></param>
        /// <param name="answers"></param>
        /// <returns></returns>
        public int SetNewSafeQuestionOrAnswers(string userid, string questioners,string answers)
        {
            DbParameter[] getParms ={
                                       DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NChar,60,userid),
                                       DbHelper.MakeInParam("@questioners",(DbType)SqlDbType.NChar,40,questioners),
                                       DbHelper.MakeInParam("@answers",(DbType)SqlDbType.NVarChar,50,answers)
                                   };
            return DbHelper.ExecuteNonQuery(CommandType.Text, string.Format("UPDATE [dbo].[{0}user] SET [questioners]=@questioners,[answers]=@answers WHERE [userid]=@userid", BaseConfigs.GetTablePrefix), getParms);
        }


        // /// <summary>
        ///// 批量删除离线用户
        ///// </summary>
        ///// <returns></returns>
        //public static int DeleteLeaveUser()
        //{
        //    DbParameter[] parms={
        //                            DbHelper.MakeInParam("@gettimes",(DbType)SqlDbType.DateTime,25,Utils.GetTime()),
        //                            DbHelper.MakeInParam("@timeout",(DbType)SqlDbType.Int,GeneralConfigs.GetConfig().Deletingexpireduserfrequency)
        //                        };
        //    return DbHelper.ExecuteNonQuery(CommandType.Text, string.Format("DELETE FROM [{0}online] WHERE datediff(s,[requesttime],@gettimes)/60)>=@timeout", BaseConfigs.GetTablePrefix), parms);
        //}
        #endregion

       


    }
}